__author__ = 'aalexeev'
import xlrd, xlwt
import os
import PythonExportTC
from selenium import webdriver
from selenium.webdriver.support.select import Select
import os, errno
import subprocess
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.common.exceptions import TimeoutException
from selenium.webdriver.common.by import By
from cx_Oracle import DatabaseError
import cx_Oracle
from cx_Oracle import Cursor


def main():
    print("this comes to be the main execution subprogramm")
    # read from excel data
    readFromExcel("test.xlsx")
   # print connectDatabase("63118703", "MY0058")


def readFromExcel(excelpath):
    rb = xlrd.open_workbook(excelpath)
    # Choose active Excel list
    cellRange = range(12, 350, 1)
    sheet = rb.sheet_by_index(2)
    # oracle connection string
    connect_address = "BPMS_SCHEMA2/Blob_TBS1@mo10625k.mspr.detemobil.de:51521/pBPMS1"
    connect_script = "@" + os.getcwd() + "\select.sql"
    for k in cellRange:
        val = sheet.row_values(k)[2]
        standort = val.split(" ")[1]
        OSSID = val.split(" ")[2]
        print("Standort="+standort+"\t"+"OSS ID="+OSSID+";")
        result=connectDatabase(OSSID, standort)
        if (result!=-1):
            OSSPID= result.split(",")[0]
            STANDORTPID=result.split(",")[1]

        # print("Standort=" + standort + " " + "OSS Node ID=" + OSSID)
        # print("Try to delete file if exist")
        # if os.path.exists(os.getcwd() + "\output.txt"):
        #     print("file deleting")
        #     os.remove(os.getcwd() + "\output.txt")
        # print("File delete ended")
        # rc = subprocess.call(
        #     ["sqlplus", "-s", connect_address, connect_script, OSSID, standort, ">" + os.getcwd() + "\output.txt"],
        #     shell=True)
        # # testing_suit= unittest.BaseTestSuite()
        #
        # # print("result=" + str(rc) + "\n")
        # filename = os.getcwd() + "\output.txt"
        # fo = open(filename)
        # PID_NET = fo.readline()
        # PID_MEASURE = fo.readline()
        # print(PID_NET + " " + PID_MEASURE + " \n")
        # fo.close()
        runTestCase(OSSPID, STANDORTPID)
        break


def connectDatabase(net_oss_pid, massnahme_pid):
    # node id var
    oss=""
    massnahme=""
    db = cx_Oracle.connect("BPMS_SCHEMA2/Blob_TBS1@mo10625k.mspr.detemobil.de:51521/pBPMS1")
    cursor1 = db.cursor()
    cursor2 = db.cursor()
    sql1ToExecute1 = "SELECT PID FROM RNIP_NETWORKELEMENTPROCESS WHERE OSS_NODE_ID=:net_oss_pid"
    sql1ToExecute2 = "SELECT PID FROM RNIP_MEASUREPROCESS WHERE SITE_ID=:massnahme_pid"
    # print sql1ToExecute1
    # print sql1ToExecute2
    result1 = cursor1.execute(sql1ToExecute1,net_oss_pid=net_oss_pid)
    result2 = cursor2.execute(sql1ToExecute2,massnahme_pid=massnahme_pid)
    first_list=[]
    second_list=[]
    for column_1 in cursor1.fetchall():
        first_list.append(column_1[0])
    for column_1 in cursor2.fetchall():
        second_list.append(column_1[0])
    if (len(second_list)==1):
        oss = first_list[0]
        massnahme = second_list[0]
        ret_string=oss+","+massnahme
        return ret_string
    else:
        print "more than 1 element ("+str(len(second_list))+")"
        return "-1"


    # list=cursor2.fetchvars
    # print list
    # if len(list)!=1:
    #     return "-1"
    # else:
    #     oss_node_pid = cursor1.fetchone()
    #     massnahme_pid = cursor2.fetchone()
    #     return str(oss_node_pid[0]) + "," + str(massnahme_pid[0])
    #
    #     #
    #     # if (result2.rowcount==1):
    #     # cursor.close()
    #     #     db.close()
    #     #     print "non unique record int database"
    #     #     return "-1"
    #     # else:
    #     #     network_process_pid=result1.fetchone()
    #     #     measure_process_pid=result2.fetchone()
    #     #     print network_process_pid
    #     #     print measure_process_pid
    #     #     cursor.close()
    #     #     db.close()
    #     #     return network_process_pid, measure_process_pid


def runTestCase(PID_NET, PID_MEASURE):
    # setup firefox
    profile = webdriver.FirefoxProfile()
    profile.set_preference("network.proxy.type", 2)
    profile.set_preference("network.proxy.autoconfig_url", "http://wpadspb.t-systems.ru/wpad.dat")
    profile.update_preferences()
    driver = webdriver.Firefox(firefox_profile=profile)
    base_url = "https://www.steps.t-mobile.de/"
    driver.implicitly_wait(30)
    verificationErrors = []
    accept_next_alert = True
    driver.get(base_url)
    driver.get(base_url + "/")
    driver.find_element_by_id("wm_login-username").send_keys("Administrator")
    driver.find_element_by_id("wm_login-password").clear()
    driver.find_element_by_id("wm_login-password").send_keys("Owermaen_43")
    driver.find_element_by_css_selector("button.portlet-form-button").click()
    driver.get("https://www.steps.t-mobile.de/meta/default/wm_xt_fabricfolder/0000007109")
    # driver.find_element_by_xpath("//li[@id='wmp7220anchor-/meta/default/wm_xt_fabricfolder/0000006939']/a/span").click()
    driver.get("https://www.steps.t-mobile.de/meta/default/wm_xt_fabricfolder/0000006939")
    driver.find_element_by_name("keywords").clear()
    driver.find_element_by_name("keywords").send_keys(PID_NET)
    el = Select(driver.find_element_by_name("refine_range"))
    el.select_by_visible_text("Custom")
    driver.find_element_by_name("dosearch").click()
    delay = 300
    try:
        WebDriverWait(driver, delay).until(EC.presence_of_element_located((By.CLASS_NAME, "portlet-section-header")))
        print("Page is ready!")
    except TimeoutException:
        print("Loading took too much time!")
        # driver.find_element_by_id("wm_login-username").clear()
    driver.get(
        "javascript:viewDetail_wmp6961('" + PID_NET + "',%20'1',%20'MBLProcesses/MBL/UMTSNBM/Netzelementdeployment',%20'6');")
    # driver.close()


main()